﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using saigonbook.Biz;

namespace saigonbook.Dal
{
    public class DAL_donhang
    {
        public static List<BIZ_donhang> Select_donhang()
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from DONHANG");
            List<BIZ_donhang> list = new List<BIZ_donhang>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_donhang donhang = new BIZ_donhang((int)row[0], DAL_donhang.GetUserById(row[1].ToString()),
                            row[2].ToString(), (int)row[3], (DateTime)row[4], (DateTime)row[5], row[6].ToString());
                list.Add(donhang);
            }
            return list;
        }

        private static BIZ_user GetUserById(string username)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from [user] where username='" + username + "'");
            BIZ_user result = null;
            foreach (DataRow row in dt.Rows)
            {
                result = new BIZ_user(row[0].ToString(), row[1].ToString(),
                            DAL_user.GetPhanQuyenById((int)row[2]), row[3].ToString(),
                            (DateTime)row[4], (int)row[5], row[6].ToString(), row[7].ToString(), row[8].ToString());
            }
            return result;
        }

        public static List<BIZ_chitietdonhang> Select_chitietdonhang()
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from CHITIETDONHANG");
            List<BIZ_chitietdonhang> list = new List<BIZ_chitietdonhang>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_chitietdonhang ctpn = new BIZ_chitietdonhang(DAL_donhang.GetDonHangById((int)row[0]),
                            DAL_sach.GetSachById((int)row[1]), (int)row[2]);
                list.Add(ctpn);
            }
            return list;
        }

        public static List<BIZ_chitietdonhang> Select_chitietdonhang(BIZ_sach sach)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from CHITIETDONHANG where masach = N'"+ sach.masach +"'");
            List<BIZ_chitietdonhang> list = new List<BIZ_chitietdonhang>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_chitietdonhang ctpn = new BIZ_chitietdonhang(DAL_donhang.GetDonHangById((int)row[0]),
                            DAL_sach.GetSachById((int)row[1]), (int)row[2]);
                list.Add(ctpn);
            }
            return list;
        }

        public static List<BIZ_chitietdonhang> Select_chitietdonhang(int madonhang)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from CHITIETDONHANG where madonhang = N'" + madonhang + "'");
            List<BIZ_chitietdonhang> list = new List<BIZ_chitietdonhang>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_chitietdonhang ctpn = new BIZ_chitietdonhang(DAL_donhang.GetDonHangById((int)row[0]),
                            DAL_sach.GetSachById((int)row[1]), (int)row[2]);
                list.Add(ctpn);
            }
            return list;
        }

        private static BIZ_donhang GetDonHangById(int madonhang)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from donhang where madonhang='" + madonhang + "'");
            BIZ_donhang result = null;
            foreach (DataRow row in dt.Rows)
            {
                result = new BIZ_donhang((int)row[0], DAL_donhang.GetUserById(row[1].ToString()),
                            row[2].ToString(), (int)row[3], (DateTime)row[4], (DateTime)row[5], row[6].ToString());
            }
            return result;
        }

        public static void Them_donhang(BIZ_donhang dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Insert into donhang(madonhang,username,diachigiaohang,tongtien,ngaymuahang,ngaygiaohang,tinhtrang) VALUES (N'" + dto.madonhang + "',N'" + dto.user.username + "',N'" + dto.diachigiaohang + "',N'" + dto.tongtien +"',N'" + dto.ngaymuahang.Date.ToString("MM-dd-yyyy") +"',N'" + dto.ngaygiaohang.Date.ToString("MM-dd-yyyy") +"',N'" + dto.tinhtrang + "')");
        }

        public static void Them_chitietdonhang(BIZ_chitietdonhang dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Insert into chitietdonhang(madonhang,masach,soluong) VALUES (N'" + dto.donhang.madonhang + "',N'" + dto.sach.masach + "',N'" + dto.soluong + "')");
        }

        public static void Capnhat_donhang(BIZ_donhang donhang)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Update donhang set diachigiaohang=N'" + donhang.diachigiaohang + "', ngaygiaohang = N'"+donhang.ngaygiaohang.Date.ToString("MM-dd-yyyy")+"', tinhtrang=N'"+donhang.tinhtrang+"'" + " where madonhang= N'" + donhang.madonhang + "'");
        }

        public static void Xoa_chitietdonhang(int ma)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Delete from chitietdonhang where madonhang= '" + ma + "'");
        }

        public static void Xoa_donhang(BIZ_donhang donhang)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Delete from donhang where madonhang= '" + donhang.madonhang + "'");
        }
    }
}
